package cn.whut.glab.dao;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.whut.glab.domain.BD_Permission;
import cn.whut.glab.domain.BD_Student;
import cn.whut.glab.domain.BD_Teacher;
import cn.whut.glab.utils.DataSourceUtils;
import cn.whut.glab.utils.StringUtil;
public class StudentDao {
	public List<BD_Student> getStudentList(int rows, int page,BD_Student student) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql1 = "select * from BD_Student ";
		if(student!=null&&StringUtil.isNotEmpty(student.getName())){
			sql1=sql1+" where Name like '%"+student.getName()+"%'";
		}
		String sql2=" limit "+( page-1) + "," + rows;
		String sql=sql1+sql2;
		return runner.query(sql, new BeanListHandler<BD_Student>(BD_Student.class));
//		String sql = "select * from BD_Student limit " +( page-1) + "," + rows;
//		//System.out.println("查询到数据库");
//		return runner.query(sql, new BeanListHandler<BD_Student>(BD_Student.class));
	}

	public int getStudentCount() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(1) from BD_Student";
		Long query = (Long) runner.query(sql, new ScalarHandler());
		return query.intValue();
	}
	public void deleteStudent(String Id) throws SQLException{
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="delete from BD_Student where Id='"+Id+"'";
		//System.out.println(sql);
		runner.update(sql);
	}
	
	public void addStudent(BD_Student student) throws SQLException{
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="insert into BD_Student(name,StudentID,gender,birthday,NativePlace,Tel,Email,qq,StartYear,GraduationYear,TypeTrain,Instructor,GraSource,Cultivate,Level,Graduation,Job) values('" + student.getName()
		+ "','" + student.getStudentID() + "','" + student.getGender() + "','" + student.getBirthday() + "','" + student.getNativePlace()
		+ "','" + student.getTel() + "','" + student.getEmail() + "','" + student.getQQ() + "','" +student.getStartYear() + "','" +
        student.getGraduationYear() + "','" + student.getTypeTrain() + "','" + student.getInstructor() + "','" + student.getGraSource() + "','" + 
        student.getCultivate() + "','" + student.getlevel() + "','" + student.getGraduation() + "','" + student.getJob() + "')";
		System.out.println(sql); 
		runner.update(sql);
	} 
	
	public void editStudent(BD_Student student, String Id) throws SQLException{
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="update BD_Student set name='" + student.getName() + "',StudentID='" + student.getStudentID() + "',gender='" + student.getGender()
		+ "',birthday='" + student.getBirthday() + "',NativePlace='" + student.getNativePlace() + "',Tel='"
		+ student.getTel() + "',Email='" +student.getEmail() + "',qq='" + student.getQQ() + "',StartYear='" + student.getStartYear() + "',GraduationYear='" + student.getGraduationYear() + "',TypeTrain='" +
		 student.getTypeTrain() + "',instructor='" + student.getInstructor() + "',grasource='" + student.getGraSource() + "',Cultivate='" + student.getCultivate() + "',Level='" + student.getlevel() + "',Graduation='" + 
		student.getGraduation() + "',Job='" + student.getJob() + "'where Id='"+Id+"'";
		runner.update(sql);
		System.out.println(sql); 
	}
}

